local schema = require("lapis.db.schema")
local types  = schema.types
local db     = require("lapis.db")

return {
	[1] = function()
		schema.create_table("roles", {
			{ "id", types.tinyint({ unsigned = true })}, -- id TINYINT UNSIGNED NOT NULL
			"name VARCHAR(20) NOT NULL",
			"CONSTRAINT roles_id_pk PRIMARY KEY(id)",
			"CONSTRAINT roles_name_uk UNIQUE(name)"
		})

		schema.create_table ("users", {
			{ "id",        types.integer({ unsigned = true, auto_increment = true })},  -- id INT UNSIGNED AUTO_INCREMENT NOT NULL
			{ "role_id",   types.tinyint({ unsigned = true, default = 3 })},            -- role_id TINYINT UNSIGNED NOT NULL DEFAULT 3
			{ "name",      types.varchar },                                             -- name VARCHAR(255) NOT NULL
			{ "username",  types.varchar },                                             -- username VARCHAR(255) NOT NULL
			{ "email",     types.varchar },                                             -- email VARCHAR(255) NOT NULL
			{ "password",  types.varchar },                                             -- password VARCHAR(255) NOT NULL
			{ "activated", types.boolean({ default = false })},                         -- activated BOOLEAN DEFAULT 0 NOT NULL
			"creation_date DATETIME",
			"CONSTRAINT users_id_pk PRIMARY KEY(id)",
			"CONSTRAINT users_username_uk UNIQUE(username)",
			"CONSTRAINT users_email_uk UNIQUE(email)",
			"CONSTRAINT users_role_id_fk FOREIGN KEY(role_id) REFERENCES roles(id) ON UPDATE RESTRICT ON DELETE RESTRICT"
		})

		schema.create_table("verifications", {
			{ "id",         types.integer({ unsigned = true })},                 -- id INT UNSIGNED NOT NULL
			{ "hash",       types.varchar({ null     = true })},                 -- token VARCHAR(255)
			{ "expiration", types.integer({ unsigned = true, default = 0 })},    -- expiration INT UNSIGNED DEFAULT 0 NOT NULL
			"CONSTRAINT verifications_id_pk PRIMARY KEY(id)",
			"CONSTRAINT verifications_id_fk FOREIGN KEY(id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE"
		})

		schema.create_table("profiles", {
			{ "id",              types.integer({ unsigned = true })},     -- id INT UNSIGNED AUTO_INCREMENT NOT NULL
			{ "avatar_file",     types.varchar({ null     = true })},     -- avatar_file VARCHAR
			{ "background_file", types.varchar({ null     = true })},     -- background_file VARCHAR(255)
			{ "description",     types.varchar({ null     = true })},     -- description VARCHAR(255)
			"CONSTRAINT profiles_id_pk PRIMARY KEY(id)",
			"CONSTRAINT profiles_id_fk FOREIGN KEY(id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT profiles_avatar_file_uk UNIQUE(avatar_file)",
			"CONSTRAINT profiles_background_file_uk UNIQUE(background_file)"
		})

		schema.create_table("articles", {
			{ "id",           types.integer({ unsigned = true, auto_increment = true })}, -- id INT UNSIGNED AUTO_INCREMENT NOT NULL
			{ "user_id",      types.integer({ unsigned = true })},                        -- user_id INT UNSIGNED NOT NULL
			"title VARCHAR(243) NOT NULL",                                                -- 255-12 = 253 - 12 caracteres de /9999/12/31/
			{ "synopsis",     types.varchar({ null = true })},                            -- synopsis VARCHAR(255) NOT NULL
			"content MEDIUMTEXT NOT NULL",
			{ "slug",         types.varchar},                                             -- slug VARCHAR(255) NOT NULL
			{ "cover_file",   types.varchar({ null = true })},                            -- cover_file VARCHAR(255)
			{ "authorized",   types.boolean({ default = false })},                        -- authorized BOOLEAN DEFAULT 0 NOT NULL
			"creation_date DATETIME NOT NULL",
			"modification_date DATETIME",
			"CONSTRAINT articles_id_pk PRIMARY KEY(id)",
			"CONSTRAINT articles_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT articles_url_uk UNIQUE(slug)",
			"CONSTRAINT articles_cover_file_uk UNIQUE(cover_file)"
		})

		schema.create_table("categories", {
			{ "id", types.integer({ unsigned = true, auto_increment = true })}, -- id INT UNSIGNED AUTO_INCREMENT NOT NULL
			"name VARCHAR(50) NOT NULL",
			"CONSTRAINT categories_id_pk PRIMARY KEY(id)",
			"CONSTRAINT categories_name_uk UNIQUE(name)"
		})

		schema.create_table("categories_article", {
			{ "article_id",   types.integer({ unsigned = true })}, -- article_id INT UNSIGNED NOT NULL
			{ "categorie_id", types.integer({ unsigned = true })}, -- categorie_id INT UNSIGNED NOT NULL
			"CONSTRAINT categories_article_pk PRIMARY KEY(article_id,categorie_id)",
			"CONSTRAINT categories_article_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT categories_categorie_id_fk FOREIGN KEY(categorie_id) REFERENCES categories(id) ON UPDATE RESTRICT ON DELETE RESTRICT"
		})

		schema.create_table("authorizations", {
			{ "article_id",         types.integer({ unsigned = true })}, -- article_id INT UNSIGNED NOT NULL
			{ "user_id",            types.integer({ unsigned = true })}, -- user_id INT UNSIGNED NOT NULL
			"authorization_date DATETIME NOT NULL",
			"CONSTRAINT authorizations_pk PRIMARY KEY(article_id,authorization_date)",
			"CONSTRAINT authorizations_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT authorizations_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT"
		})

		schema.create_table("comments", {
			{ "user_id",      types.integer({ unsigned = true })},  -- user_id INT UNSIGNED NOT NULL
			{ "article_id",   types.integer({ unsigned = true })},  -- article_id INT UNSIGNED NOT NULL
			{ "body",         types.varchar },                      -- body VARCHAR(255) NOT NULL
			"comment_date DATETIME NOT NULL",
			"CONSTRAINT comments_pk PRIMARY KEY(user_id,comment_date)",
			"CONSTRAINT comments_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT comments_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
		})

		schema.create_table("favorites", {
			{"user_id",    types.integer({ unsigned = true })}, -- user_id INT UNSIGNED NOT NULL
			{"article_id", types.integer({ unsigned = true })}, -- article_id INT UNSIGNED NOT NULL
			"CONSTRAINT favorites_pk PRIMARY KEY(user_id,article_id)",
			"CONSTRAINT favorites_user_id_fk FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
			"CONSTRAINT favorites_article_id_fk FOREIGN KEY(article_id) REFERENCES articles(id) ON UPDATE RESTRICT ON DELETE RESTRICT",
		})

		db.query("INSERT INTO roles(id,name) VALUES(?,?),(?,?),(?,?)", 1, "ADMINISTRATOR", 2, "MODERATOR", 3, "USER")
	end
}